package com.java2007.hotel.dao.impl;

import com.java2007.hotel.dao.FoodDao;
import com.java2007.hotel.entity.PageBean;
import com.java2007.hotel.pojo.Food;
import com.java2007.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Reckeys
 * @version V1.0
 * @date 2021/01/23
 **/
public class FoodDaoImpl implements FoodDao {
    /**
     * 通过菜名模糊查询
     * @param foodName
     * @return
     */
    @Override
    public List<Food> findByFoodName(String foodName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id foodId,food_name foodName,f.food_type_id foodTypeId,t.type_name foodTypeName,food_price foodPrice,food_vip_price foodVipPrice,food_image foodImage,food_desc foodDesc,food_create_time foodCreateTime,food_update_time foodUpdateTime,food_creat_user_id foodCreatUserId FROM t_food f LEFT JOIN t_food_type t ON  f.food_type_id = t.type_id WHERE f.food_name LIKE ?";
        return queryRunner.query(sql,new BeanListHandler<>(Food.class),foodName);
    }

    /**
     * 通过foodId删除
     * @param foodId
     * @return
     */
    @Override
    public int deleteByFoodId(Integer foodId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "DELETE FROM t_food WHERE food_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,foodId);
    }

    /**
     * 通过foodId查询，并回显
     * @param foodId
     * @return
     */
    @Override
    public Food findByfoodId(Integer foodId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id foodId,food_name foodName,f.food_type_id foodTypeId,t.type_name foodTypeName,food_price foodPrice,food_vip_price foodVipPrice,food_image foodImage,food_desc foodDesc,food_create_time foodCreateTime,food_update_time foodUpdateTime,food_creat_user_id foodCreatUserId FROM t_food f LEFT JOIN t_food_type t ON  f.food_type_id = t.type_id WHERE f.food_id = ?";

        //String sql = "SELECT food_id foodId,food_name foodName,food_type_id foodTypeId,food_price foodPrice,food_vip_price foodVipPrice,food_image foodImage,food_desc foodDesc,food_create_time foodCreateTime,food_update_time foodUpdateTime,food_creat_user_id foodCreatUserId FROM t_food WHERE food_id = ?";
        return queryRunner.query(sql,new BeanHandler<>(Food.class),foodId);
    }

    /**
     * 添加菜信息
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public int addFood(Food food) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "INSERT INTO t_food (food_name,food_type_id,food_price,food_vip_price,food_image,food_desc,food_create_time,food_creat_user_id)VALUES(?,?,?,?,?,?,?,?)";
        return queryRunner.update(JdbcUtils.getConnection(),sql,food.getFoodName(),food.getFoodTypeId(),food.getFoodPrice(),food.getFoodVipPrice(),food.getFoodImage(),food.getFoodDesc(),food.getFoodCreateTime(),food.getFoodCreatUserId());
    }

    /**
     * 更新菜信息
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public int updateFood(Food food) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "UPDATE t_food SET food_name = ?,food_type_id = ?,food_price = ?,food_vip_price = ?,food_image = ?,food_desc = ?,food_update_time = ? WHERE food_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,food.getFoodName(),food.getFoodTypeId(),food.getFoodPrice(),food.getFoodVipPrice(),food.getFoodImage(),food.getFoodDesc(),food.getFoodUpdateTime(),food.getFoodId());
    }

    @Override
    public List<Food> findFoodToPage(String sql,List<Object> params) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        return queryRunner.query(sql,new BeanListHandler<>(Food.class),params.toArray());
    }

    /**
     * 查询总数量
     * @param sql
     * @param params
     * @return
     */
    @Override
    public Long findCount(String sql, List<Object> params) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        return queryRunner.query(sql,new ScalarHandler<Long>(),params.toArray());
    }

    /**
     * 按名字查询
     * @param foodName
     * @return
     */
    @Override
    public Food findFood(String foodName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id foodId,food_name foodName,f.food_type_id foodTypeId,t.type_name foodTypeName,food_price foodPrice,food_vip_price foodVipPrice,food_image foodImage,food_desc foodDesc,food_create_time foodCreateTime,food_update_time foodUpdateTime,food_creat_user_id foodCreatUserId FROM t_food f LEFT JOIN t_food_type t ON  f.food_type_id = t.type_id WHERE f.food_name = ?";
        return queryRunner.query(sql,new BeanHandler<>(Food.class),foodName);
    }


}
